package com.aligotec.lototron.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DataBase {
	public enum TABLE {
		LOTTERIES, ENTITIES, COUNTRIES, ODDS, EVENTS
	};
	private Logger log = Logger.getLogger("DataBase");
	private String file = "lototron.db";
	
	private Connection conn = null;
	
	private Connection getConnection(){
		try {
			if(conn != null){
				return conn;
			}else{
				log.log(Level.INFO, "Opening connection to " + this.file);
				Class.forName("org.sqlite.JDBC");
				conn = DriverManager.getConnection("jdbc:sqlite:" + file);
				return conn;
			}
		} catch (ClassNotFoundException e) {
			log.log(Level.SEVERE, e.toString());
			e.printStackTrace();
			return null;
		} catch (SQLException e) {
			log.log(Level.SEVERE, e.toString());
			e.printStackTrace();
			return null;
		}
	}
	
	private Statement newStatement(){
		try {
			getConnection();
			if(conn != null){
				return conn.createStatement();
			}else{
				return null;
			}
		} catch (SQLException e) {
			log.log(Level.SEVERE, e.toString());
			e.printStackTrace();
			return null;
		}
	}
	
	public PreparedStatement preparedStatement(String sqlQuery){
		log.log(Level.INFO, "preparedStatement: " + sqlQuery);
		getConnection();
		if(conn != null){
			try {
				return conn.prepareStatement(sqlQuery);
			} catch (SQLException e) {
				log.log(Level.SEVERE, e.toString());
				e.printStackTrace();
				return null;
			}
		}else{
			return null;
		}
	}
	
	public int executeUpdate(String sqlQuery){
		log.log(Level.INFO, "executeUpdate: " + sqlQuery);
		Statement st = newStatement();
		if(st != null){
			try {
				int resp = st.executeUpdate(sqlQuery);
				log.log(Level.INFO, "executeUpdate Response: " + resp);
				return resp;
			} catch (SQLException e) {
				log.log(Level.SEVERE, e.toString());
				e.printStackTrace();
				return -1;
			}
		}else{
			return -1;
		}
	}
	
	public ResultSet executeQuery(String sqlQuery){
		log.log(Level.INFO, "executeQuery: " + sqlQuery);
		Statement st = newStatement();
		if(st != null){
			try {
				ResultSet results = st.executeQuery(sqlQuery);
				log.log(Level.INFO, "executeUpdate Response: ok");
				return results;
			} catch (SQLException e) {
				log.log(Level.SEVERE, e.toString());
				e.printStackTrace();
				return null;
			}
		}else{
			return null;
		}
	}
	
	public void closeConnection(){
		if(conn != null){
			try {
				log.log(Level.INFO, "Closing connection to " + this.file);
				conn.close();
			} catch (SQLException e) {
				log.log(Level.SEVERE, e.toString());
				e.printStackTrace();
			}
		}
	}
	
	public DataBase() throws Exception{}
	
	public int dropTable(TABLE name){
		switch(name){
		case COUNTRIES: return executeUpdate("drop table if exists countries;");
		case ODDS: return executeUpdate("drop table if exists odds;");
		case ENTITIES: return executeUpdate("drop table if exists entities;");
		case EVENTS: return executeUpdate("drop table if exists events;");
		default: return -1;
		}
	}
	
	public int createTable(TABLE name){
		String sql = "";
		dropTable(name);
		switch(name){
		case COUNTRIES: sql = "CREATE TABLE countries (" +
					"cou_id INTEGER UNIQUE," +
					"cou_vc_name VARCHAR(100)" +
					");";
				break;
		case ENTITIES: sql = "CREATE TABLE entities (" +
					"entid INTEGER UNIQUE," +
					"ent_vc_name VARCHAR(400)," +
					"ent_nu_idcountry INTEGER," +
					"ent_dt_start DATE," +
					"ent_dt_end" +
					");";
				break;
		case EVENTS: sql = "CREATE TABLE events (" +
				"evn_id INTEGER UNIQUE," +
				"evn_vc_code VARCHAR(40)," +
				"evn_vc_name VARCHAR(100)," +
				"evn_dt_event DATE" +
				");";
			break;
		case ODDS: sql = "CREATE TABLE odds (" +
				"odd_id INTEGER UNIQUE," +
				"odd_idevent INTEGER," +
				"odd_vc_name VARCHAR(30)," +
				"odd_nu_bet DATE" +
				");";
			break;
		}
		return executeUpdate(sql);
	}
	
	
	public void createTable() throws SQLException{
		/*executeUpdate("drop table if exists people;");
        executeUpdate("create table people (name, occupation);");
        PreparedStatement prep = preparedStatement(
            "insert into people values (?, ?);");

        prep.setString(1, "Gandhi");
        prep.setString(2, "politics");
        prep.addBatch();
        prep.setString(1, "Turing");
        prep.setString(2, "computers");
        prep.addBatch();
        prep.setString(1, "Wittgenstein");
        prep.setString(2, "smartypants");
        prep.addBatch();

        conn.setAutoCommit(false);
        prep.executeBatch();
        conn.setAutoCommit(true);

        ResultSet rs = executeQuery("select * from people;");
        while (rs.next()) {
            System.out.println("name = " + rs.getString("name"));
            System.out.println("job = " + rs.getString("occupation"));
        }
        rs.close();
        conn.close();*/
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			DataBase db = new DataBase();
			db.createTable(DataBase.TABLE.EVENTS);
			db.createTable(DataBase.TABLE.ODDS);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}
